"""
一、通过Python执行多条语句接口完成处理数据的准备、向数据库中表传输数据
在tianya数据库中建立table_date表格（含年、月、日）
use tianya;
CREATE TABLE table_date(
year varchar(255),
month varchar(255),
day varchar(255)
)DEFAULT charset=utf8mb4;
"""

import random
import pymysql

#连接tianya数据库
navicat_database_cfg = {
    'host': 'localhost',
    'user': 'root',
    'password': 'test1234',
    'database': 'tianya'
}
db_connect = pymysql.connect(**navicat_database_cfg)
db_cursor = db_connect.cursor()


#建立日期，并向数据库导入日期
def insert_date():
    date_arry = []
    year = 2020
    for i in range(5):
        month = random.randint(1, 12)
        day = random.randint(1, 31)
        date_arry.append((year, month, day))
    SQL = f"""insert into table_date(year, month, day)values(%s, %s, %s);"""
    db_cursor.executemany(SQL, date_arry)
    db_cursor.close()
    db_connect.commit()
    db_connect.close()


if __name__ == "__main__":
    insert_date()
'''
二、练习Mysql的CRUD操作.
2.1创建(Create)：
    单条插入：
            insert into table_date_test(day) values("day1")
    多条插入:
            insert into table_date_test(year,month,day) values("year1","month1","day1"),("year2","month2","day2")
2.2检索(Retrieve)
     条件查询:
            select month, day from table_date_test where month = "month1" and day ="day1"
     模糊匹配
            select month from table_date_test where month like "%on%"
     限制返回记录数
            select month from table_date_test where month like "%on%" limit 1
     过滤重复值
            select distinct(month) from table_date_test
     获取总数
            select count(*) from table_date_test
     排序
            select * from table_date_test order by month asc
            select * from table_date_test order by month desc
2.3更新(Update)
        update table_date_test set month="month11" where month="month1"
2.4删除(DELETE)
        delete from table_date_test where  month="month11"
    删除数据，保留表结构
        truncate table_date_test
'''